# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
文件：inital_data_msydloan
中移动项目数据初始化:
1.授信>借款 成功数据
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import time
from msydwork.api.api_msydloan import block
from msydwork.config.config_msydloan import db, cursor
from pycacho.cachobase.file_deal import get_line
from pycacho.cachobase.deal_time import add_date, minus_date
from pycacho.cachobase.get_base_info import process
from pycacho.cachobase.logger import Logger

logger = Logger("inital_data_msydloan").get_log()


td_date = minus_date(0,'t')     # 当前日期
exp_dt = minus_date(-30,'t')    # 额度失效时间

st_now = minus_date(0,'qt')
up_now = minus_date(0,'qt')


def truncate_table():
    logger.info("开始进行数据库数据初始化-清除数据")
    data = get_line('F:\PythonProject\msydwork\\test\\msydloandb-clear.sql','TRUNCATE','跳出')
    for sql in data:
        print(sql)
        cursor.execute(sql)
        db.commit()


def drop_table():
    logger.info("开始进行数据库数据初始化-删除自动任务数据库表")
    data = get_line('F:\PythonProject\msydwork\\test\\cronjob-drop.sql', 'DROP','跳出')
    for sql in data:
        try:
            cursor.execute(sql)
            db.commit()
            print(sql)
        except:
            logger.info("自动任务数据库表已经被删除......")

# 借款成功、放款成功loan
def insert_info(type):
    if type == 1:
        file = 'F:\PythonProject\msydwork\\test\\msydloandb-apply3.sql'
    else:
        file = 'F:\PythonProject\msydwork\\test\\msydloandb-loan4.sql'
    logger.info("开始进行数据库数据初始化-插入数据到数据库表")
    data = get_line(file,'INSERT''跳出')
    for sql in data:
        try:
            cursor.execute(sql)
            db.commit()
            print(sql)
        except:
            logger.info("数据库表已经有记录......")


def insert_CW_main(id,process_no,type,qryCreditId,usr_no,hb_usrNo,name,phone):
    sql = "INSERT INTO pro_main VALUES ('%s', '%s', '%s', 'P', NULL, '%s', '%s', '%s', '%s', '%s', 'HB', '002', 2000.00, '%s', '%s', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '%s', 'SYS', '%s', 'SYS')" %(id,process_no,type,qryCreditId,usr_no,hb_usrNo,name,phone,td_date,exp_dt,st_now,up_now)
    try:
        cursor.execute(sql)
    except Exception as e:
    # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()  # 事务提交
        print(process_no + '插入授信信息到pro_main成功！')


def insert_LW_main(id,process_no,type,qryCreditId,usr_no,hb_usrNo,name,phone,brw_ord_dt):
    sql = "INSERT INTO pro_main VALUES ('%s', '%s', '%s', 'P', NULL, '%s', '%s', '%s', '%s', '%s', 'HB', '002', NULL, NULL, NULL, NULL, '%s', '%s', 24, NULL, NULL, '888190900238401', '民盛互联网小额贷款', 50, '002', '%s', 'SYS', '%s', 'SYS')" %(id,process_no,type,qryCreditId,usr_no,hb_usrNo,name,phone,brw_ord_dt,amt,st_now,up_now)
    try:
        cursor.execute(sql)
    except Exception as e:
    # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()  # 事务提交
        print(process_no + '插入借款信息到pro_main成功！')


def insert_hb_usr(id,usr_no,qryCreditId,hb_usrNo,phone,name,id_card):
    sql = "INSERT INTO hb_usr VALUES ('%s', '%s', 2000.00, '%s', 'F', '%s', '%s', NULL, '%s', '%s', '%s', '%s', '%s', '412007', '071', '北京', '002', '', '6228481719176834', '111', '1837468', 'ICBC', '', '', '', '', '2', '1', 90, '004', 'HB', '001', '', '', '', '', '', '2019-11-01', '', '', '', '', '', '1', '2029-11-20', '', '', '', NULL, '', '17', '', '%s', 'SYS', '%s', 'SYS')" %(id,usr_no,amt,td_date,exp_dt,qryCreditId,hb_usrNo,phone,name,id_card,st_now,up_now)
    try:
        cursor.execute(sql)
    except Exception as e:
    # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()  # 事务提交
        print(hb_usrNo + '插入信息到hb_usr成功！')

def insert_hb_loan(id,process_no,qryCreditId,hb_usrNo,phone,brw_ord_dt,loan_month,prov,phone1,id_card,prov_stg_day):
    sql = "INSERT INTO pro_hb_loan VALUES ('%s','%s', '%s', '%s', '%s', '%s', '', '套餐编号', 1000.00, '%s', '%s', '071', '商品名称', '002', '159173000', '002', '营业厅名称', '159173001', 'HB', '', '', '001', '1', '%s', '031', '1', '', '', '%s', '111', '%s', '888190900238401', '%s', '%s', 'SYS', '%s', NULL)" %(id,process_no,qryCreditId,hb_usrNo,phone,brw_ord_dt,amt,loan_month,prov,phone1,id_card,prov_stg_day,st_now,up_now)
    try:
        cursor.execute(sql)
    except Exception as e:
    # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()  # 事务提交
        print(process_no + '插入信息到pro_hb_loan成功！')


# 办理套餐时判断是否是冻结状态，通知成功后为I作废
def update_usr():
    sql = "update hb_usr set credit_status='F';"
    cursor.execute(sql)
    db.commit()
    print('更新用户表授信状态为-冻结！')

def insert_seq(id,seq,type):
    sql = "INSERT INTO pro_seq VALUES ('%s','%s','%s', '%s', '%s')"%(id,seq,td_date,type,st_now)
    try:
        cursor.execute(sql)
    except Exception as e:
    # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()  # 事务提交
        print('插入信息到pro_seq成功！')


# 主流程：授信、借款、借款流程表、用户表
def flow_process(num):
    flow_pre()
    info = process(num)
    for i in range(0,3):
        qryCreditId = info[i][0]
        name = info[i][1]
        id_card= info[i][2]
        hb_usrNo = 'HB'+ id_card
        usr_no = 'CI' + id_card
        mblNo = info[i][3]
        process_cw = info[i][4]
        # 借款成功后
        process_lw = info[i][5]
        brwOrdNo = info[i][6]
        if i < 1:           # 0
            prov = '23'
            loan_month = '24'
            prov_stg_day = '10'
        elif 0< i < 3:          # 1
            prov = '28'
            loan_month = '12'
            prov_stg_day = '10'
        elif 1< i < 3:          # 2
            prov = '19'         # 广东省
            loan_month = '24'
            prov_stg_day = '10'
        elif 2< i < 4:          # 3
            prov = '19'
            loan_month = '24'
            prov_stg_day = '10'
        else:                   # 4
            prov = '18'
            loan_month = '36'
            prov_stg_day = '20'
        print(qryCreditId,name,mblNo, process_cw,process_lw, brwOrdNo)
        insert_CW_main(2*(i+1)-1, process_cw, 'C', qryCreditId, usr_no, hb_usrNo, name, mblNo)
        insert_LW_main(2*(i+1), process_lw, 'L', brwOrdNo, usr_no, hb_usrNo, name, mblNo,minus_date(1,'t'))
        insert_hb_loan(i +1, process_lw, brwOrdNo, hb_usrNo, mblNo, minus_date(1,'t'), loan_month,prov,mblNo, id_card,prov_stg_day)
        insert_hb_usr(i+1, usr_no, qryCreditId, hb_usrNo, mblNo, name, id_card)
        time.sleep(2)
        if i<2:
            block(brwOrdNo,process_lw,mblNo,minus_date(1,'s'),minus_date(1,'q'))


# 提前准备：表清除数据、自动任务表删除、案件生成seq设置
def flow_pre():
    truncate_table()
    #drop_table()
    insert_seq(1,num + 1, 'CW')    # CW,CI,LW
    insert_seq(2,num + 1, 'CI')    # CW,CI,LW
    insert_seq(3,num + 1, 'LW')    # CW,CI,LW


if __name__ == '__main__':
    amt = '1000.00'
    flow = 3     # 1-插入数据，2-数据库脚本
    num = 10
    if flow == 2:
        flow_process(num)
    elif flow == 3:
        flow_pre()
        #insert_info(2)     # 1-借款成功，2-放款成功
    else:
        pass
        #truncate_table()
        drop_table()